<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width,initial-scale=1"><title>Oracle常用操作 | Joey</title><meta name="keywords" content="数据库,Oracle"><meta name="author" content="方陈勇"><meta name="copyright" content="方陈勇"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="项目中使用到的Oracle数据库操作笔记记录。包括数据库误删恢复，处理锁定、查询历史执行记录、触发器和序列、关联更新，行转列等">
<meta property="og:type" content="article">
<meta property="og:title" content="Oracle常用操作">
<meta property="og:url" content="https://fangchenyong.top/2019/07/23/%E6%95%B0%E6%8D%AE%E5%BA%93-Oracle-Oracle%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/index.html">
<meta property="og:site_name" content="Joey">
<meta property="og:description" content="项目中使用到的Oracle数据库操作笔记记录。包括数据库误删恢复，处理锁定、查询历史执行记录、触发器和序列、关联更新，行转列等">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG">
<meta property="article:published_time" content="2019-07-22T16:00:00.000Z">
<meta property="article:modified_time" content="2020-09-25T02:49:52.776Z">
<meta property="article:author" content="方陈勇">
<meta property="article:tag" content="数据库">
<meta property="article:tag" content="Oracle">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG"><link rel="shortcut icon" href="/img/favicon.png"><link rel="canonical" href="https://fangchenyong.top/2019/07/23/%E6%95%B0%E6%8D%AE%E5%BA%93-Oracle-Oracle%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: undefined,
  localSearch: undefined,
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: '复制成功',
    error: '复制错误',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '天',
  date_suffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  source: {
    jQuery: 'https://cdn.jsdelivr.net/npm/jquery@latest/dist/jquery.min.js',
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/js/jquery.justifiedGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/justifiedGallery/dist/css/justifiedGallery.min.css'
    },
    fancybox: {
      js: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.js',
      css: 'https://cdn.jsdelivr.net/npm/@fancyapps/fancybox@latest/dist/jquery.fancybox.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isanchor: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = { 
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2020-09-25 10:49:52'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    })(window)</script><meta name="generator" content="Hexo 5.4.0"></head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="author-avatar"><img class="avatar-img" src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/3FD9B055-6361-49B7-B8CE-5BA9144BD27F.JPG" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data"><div class="data-item is-center"><div class="data-item-link"><a href="/archives/"><div class="headline">文章</div><div class="length-num">43</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/tags/"><div class="headline">标签</div><div class="length-num">51</div></a></div></div><div class="data-item is-center"><div class="data-item-link"><a href="/categories/"><div class="headline">分类</div><div class="length-num">53</div></a></div></div></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> Home</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> Archives</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> Tags</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> Categories</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> List</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/music/"><i class="fa-fw fas fa-music"></i><span> Music</span></a></li><li><a class="site-page child" href="/movies/"><i class="fa-fw fas fa-video"></i><span> Movie</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> Link</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> About</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/3FD9B055-6361-49B7-B8CE-5BA9144BD27F.JPG')"><nav id="nav"><span id="blog_name"><a id="site-name" href="/">Joey</a></span><div id="menus"><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> Home</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> Archives</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> Tags</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> Categories</span></a></div><div class="menus_item"><a class="site-page" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> List</span><i class="fas fa-chevron-down expand"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/music/"><i class="fa-fw fas fa-music"></i><span> Music</span></a></li><li><a class="site-page child" href="/movies/"><i class="fa-fw fas fa-video"></i><span> Movie</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> Link</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> About</span></a></div></div><div id="toggle-menu"><a class="site-page"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">Oracle常用操作</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2019-07-22T16:00:00.000Z" title="发表于 2019-07-23 00:00:00">2019-07-23</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2020-09-25T02:49:52.776Z" title="更新于 2020-09-25 10:49:52">2020-09-25</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a><i class="fas fa-angle-right post-meta-separator"></i><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/Oracle/">Oracle</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-wordcount"><i class="far fa-file-word fa-fw post-meta-icon"></i><span class="post-meta-label">字数总计:</span><span class="word-count">578</span><span class="post-meta-separator">|</span><i class="far fa-clock fa-fw post-meta-icon"></i><span class="post-meta-label">阅读时长:</span><span>2分钟</span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h2 id="数据库误删恢复"><a href="#数据库误删恢复" class="headerlink" title="数据库误删恢复"></a>数据库误删恢复</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">--10分钟前数据可用于误删恢复</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名_tmp <span class="keyword">as</span> </span><br><span class="line">(</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> 表名 <span class="keyword">AS</span> <span class="keyword">OF</span> <span class="type">TIMESTAMP</span> SYSDATE <span class="operator">-</span> <span class="number">10</span><span class="operator">/</span><span class="number">1440</span></span><br><span class="line">)；</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 表名_tmp；</span><br><span class="line">update 表名A  <span class="keyword">set</span> A.S_GRADE<span class="operator">=</span>(<span class="keyword">select</span> S_GRADE  <span class="keyword">from</span> 表名_TMP B <span class="keyword">where</span> A.ID<span class="operator">=</span>B.ID)；</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 表名；</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">--闪回</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 enable <span class="type">row</span> movement;</span><br><span class="line">flashback <span class="keyword">table</span> 表名 <span class="keyword">to</span> <span class="type">timestamp</span> to_timestamp(<span class="string">&#x27;2018-08-09 10:00:00&#x27;</span>,<span class="string">&#x27;yyyy-mm-dd HH24:MI:SS&#x27;</span>);</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 disable <span class="type">row</span> movement; </span><br></pre></td></tr></table></figure>

<h2 id="处理锁定"><a href="#处理锁定" class="headerlink" title="处理锁定"></a>处理锁定</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">--处理锁定</span></span><br><span class="line"><span class="keyword">select</span> t1.sid, t1.serial#, t1.username, t1.logon_time <span class="keyword">from</span> v$session t1 , v$locked_object t2</span><br><span class="line"><span class="keyword">where</span> t1.sid <span class="operator">=</span> t2.session_id <span class="keyword">order</span> <span class="keyword">by</span> t1.logon_time;</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">system</span> kill session <span class="string">&#x27;1025,1152&#x27;</span></span><br></pre></td></tr></table></figure>

<h2 id="查询历史执行记录"><a href="#查询历史执行记录" class="headerlink" title="查询历史执行记录"></a>查询历史执行记录</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">--查看oracle执行的语句</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> v$<span class="keyword">sql</span> t</span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">t.PARSING_SCHEMA_NAME <span class="operator">=</span> <span class="string">&#x27;数据库名称&#x27;</span> <span class="comment">--数据库</span></span><br><span class="line"><span class="keyword">and</span> <span class="built_in">lower</span>(t.SQL_TEXT) <span class="keyword">like</span> <span class="string">&#x27;%sql语句中包含的信息%&#x27;</span> <span class="comment">--查询某类SQL语句</span></span><br><span class="line"><span class="keyword">and</span>  t.LAST_ACTIVE_TIME<span class="operator">&gt;</span>(sysdate <span class="operator">-</span> <span class="type">interval</span> <span class="string">&#x27;1&#x27;</span> <span class="keyword">MINUTE</span>)  <span class="comment">--执行1分钟内的SQL语句</span></span><br><span class="line"><span class="keyword">and</span> (t.MODULE <span class="keyword">is</span> <span class="keyword">null</span> <span class="keyword">or</span> t.MODULE <span class="keyword">not</span> <span class="keyword">like</span> <span class="string">&#x27;%PL/SQL%&#x27;</span>) <span class="comment">--不是在某些终端里的执行</span></span><br></pre></td></tr></table></figure>

<h2 id="触发器和序列"><a href="#触发器和序列" class="headerlink" title="触发器和序列"></a>触发器和序列</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">--序列</span></span><br><span class="line"><span class="keyword">CREATE</span> SEQUENCE POWERPMS.S_NBG_MAT_APPLY</span><br><span class="line">    INCREMENT <span class="keyword">BY</span> <span class="number">1</span></span><br><span class="line">    <span class="keyword">START</span> <span class="keyword">WITH</span> <span class="number">1</span></span><br><span class="line">    MAXVALUE <span class="number">1000000000000000000000000000</span></span><br><span class="line">    NOMINVALUE</span><br><span class="line">    NOCYCLE</span><br><span class="line">    CACHE <span class="number">20</span></span><br><span class="line">    NOORDER</span><br><span class="line">GO</span><br><span class="line"><span class="comment">--触发器</span></span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">OR</span> REPLACE <span class="keyword">TRIGGER</span> POWERPMS.TID_NBG_MAT_APPLY</span><br><span class="line">  before <span class="keyword">insert</span> <span class="keyword">on</span> NBG_MAT_APPLY</span><br><span class="line">  <span class="keyword">for</span> <span class="keyword">each</span> <span class="type">row</span></span><br><span class="line"><span class="keyword">BEGIN</span></span><br><span class="line">  <span class="keyword">SELECT</span> S_NBG_MAT_APPLYT.NEXTVAL <span class="keyword">INTO</span> :NEW.OLD_UNIQUEID <span class="keyword">FROM</span> DUAL; </span><br><span class="line"><span class="keyword">END</span> TID_NBG_MAT_APPLY;</span><br><span class="line">GO</span><br></pre></td></tr></table></figure>

<h2 id="关联更新"><a href="#关联更新" class="headerlink" title="关联更新"></a>关联更新</h2><figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">MERGE</span> <span class="keyword">INTO</span>  表A <span class="keyword">USING</span>  表B <span class="keyword">ON</span> ( 表A.字段<span class="operator">=</span> 表B.字段) <span class="keyword">WHEN</span> MATCHED <span class="keyword">THEN</span></span><br><span class="line">UPDATE</span><br><span class="line"><span class="keyword">SET</span> 表A.字段 <span class="operator">=</span> 表B.字段</span><br></pre></td></tr></table></figure>

<h2 id="行转列"><a href="#行转列" class="headerlink" title="行转列"></a>行转列</h2><blockquote>
<p>10g ：wm_concat</p>
<p>11g : pivot ，unpivot </p>
<p>12c :  listagg</p>
<p><a target="_blank" rel="noopener" href="https://www.cnblogs.com/mellowsmile/p/4642306.html">参考链接</a></p>
</blockquote>
<h2 id="表空间不足"><a href="#表空间不足" class="headerlink" title="表空间不足"></a>表空间不足</h2><p>项目中使用出现报错</p>
<blockquote>
<p>ORA-01653: 表xx无法通过 8192 (在表空间 xx_data 中) 扩展。</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">-- 查询剩余表空间，发现没有要找的表空间</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">UPPER</span>(F.TABLESPACE_NAME) &quot;表空间名&quot;,D.TOT_GROOTTE_MB &quot;表空间大小(M)&quot;,D.TOT_GROOTTE_MB <span class="operator">-</span> F.TOTAL_BYTES &quot;已使用空间(M)&quot;,</span><br><span class="line">TO_CHAR(ROUND((D.TOT_GROOTTE_MB <span class="operator">-</span> F.TOTAL_BYTES)<span class="operator">/</span>D.TOT_GROOTTE_MB <span class="operator">*</span> <span class="number">100</span>,<span class="number">2</span>),<span class="string">&#x27;990.99&#x27;</span>) &quot;使用比&quot;,</span><br><span class="line">F.TOTAL_BYTES &quot;空闲空间(M)&quot;,</span><br><span class="line">F.MAX_BYTES &quot;最大块(M)&quot;</span><br><span class="line"><span class="keyword">FROM</span> (<span class="keyword">SELECT</span> TABLESPACE_NAME,</span><br><span class="line">ROUND(<span class="built_in">SUM</span>(BYTES)<span class="operator">/</span>(<span class="number">1024</span> <span class="operator">*</span> <span class="number">1024</span>), <span class="number">2</span>) TOTAL_BYTES,</span><br><span class="line">ROUND(<span class="built_in">MAX</span>(BYTES)<span class="operator">/</span>(<span class="number">1024</span> <span class="operator">*</span> <span class="number">1024</span>), <span class="number">2</span>) MAX_BYTES</span><br><span class="line"><span class="keyword">FROM</span> SYS.DBA_FREE_SPACE</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> TABLESPACE_NAME) F,</span><br><span class="line">(<span class="keyword">SELECT</span> DD.TABLESPACE_NAME,</span><br><span class="line">ROUND(<span class="built_in">SUM</span>(DD.BYTES)<span class="operator">/</span>(<span class="number">1024</span> <span class="operator">*</span> <span class="number">1024</span>),<span class="number">2</span>) TOT_GROOTTE_MB</span><br><span class="line"><span class="keyword">FROM</span> SYS.DBA_DATA_FILES DD</span><br><span class="line"><span class="keyword">GROUP</span> <span class="keyword">BY</span> DD.TABLESPACE_NAME) D</span><br><span class="line"><span class="keyword">WHERE</span> D.TABLESPACE_NAME <span class="operator">=</span> F.TABLESPACE_NAME</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> <span class="number">4</span> <span class="keyword">DESC</span>;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">-- 查看表空间是否具有自动扩展的能力   </span></span><br><span class="line"><span class="keyword">SELECT</span> T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS</span><br><span class="line"><span class="keyword">FROM</span> DBA_TABLESPACES T,DBA_DATA_FILES D</span><br><span class="line"><span class="keyword">WHERE</span> T.TABLESPACE_NAME <span class="operator">=</span>D.TABLESPACE_NAME</span><br><span class="line"><span class="keyword">ORDER</span> <span class="keyword">BY</span> TABLESPACE_NAME,FILE_NAME;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="comment">-- 修改表空间文件扩展方式:  路径是上面查询的 FILE_NAME</span></span><br><span class="line"><span class="keyword">ALTER</span> DATABASE  DATAFILE <span class="string">&#x27;D:\ORACLE\PRODUCT\10.2.0\ORADATA\NBGPMC\POWERPMS.ORA&#x27;</span> AUTOEXTEND  <span class="keyword">ON</span> NEXT <span class="number">50</span>M MAXSIZE UNLIMITED</span><br></pre></td></tr></table></figure>

</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">文章作者: </span><span class="post-copyright-info"><a href="mailto:undefined">方陈勇</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">文章链接: </span><span class="post-copyright-info"><a href="https://fangchenyong.top/2019/07/23/%E6%95%B0%E6%8D%AE%E5%BA%93-Oracle-Oracle%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/">https://fangchenyong.top/2019/07/23/%E6%95%B0%E6%8D%AE%E5%BA%93-Oracle-Oracle%E5%B8%B8%E7%94%A8%E6%93%8D%E4%BD%9C/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来自 <a href="https://fangchenyong.top" target="_blank">Joey</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a><a class="post-meta__tags" href="/tags/Oracle/">Oracle</a></div><div class="post_share"><div class="social-share" data-image="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/social-share.js/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/social-share.js/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/2019/07/23/%E6%95%B0%E6%8D%AE%E5%BA%93-Oracle-Oracle%E4%BD%BF%E7%94%A8%E6%AD%A3%E5%88%99%E8%A1%A8%E8%BE%BE%E5%BC%8F/"><img class="prev-cover" src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" onerror="onerror=null;src='/img/404.jpg'" alt="cover of previous post"><div class="pagination-info"><div class="label">上一篇</div><div class="prev_info">Oracle中使用正则表达式</div></div></a></div><div class="next-post pull-right"><a href="/2019/07/23/%E6%95%B0%E6%8D%AE%E5%BA%93-Sql%20Server-sqlserver%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B/"><img class="next-cover" src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" onerror="onerror=null;src='/img/404.jpg'" alt="cover of next post"><div class="pagination-info"><div class="label">下一篇</div><div class="next_info">SqlServer存储过程使用游标循环</div></div></a></div></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span> 相关推荐</span></div><div class="relatedPosts-list"><div><a href="/2020/04/19/数据库-MySQL-MySQL高级/" title="MySQL高级"><img class="cover" src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-04-19</div><div class="title">MySQL高级</div></div></a></div><div><a href="/2020/02/10/数据库-MySQL-MySQL基础/" title="MySQL基础"><img class="cover" src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2020-02-10</div><div class="title">MySQL基础</div></div></a></div><div><a href="/2019/07/23/数据库-Sql Server-sqlserver存储过程/" title="SqlServer存储过程使用游标循环"><img class="cover" src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2019-07-23</div><div class="title">SqlServer存储过程使用游标循环</div></div></a></div><div><a href="/2019/07/23/数据库-Sql Server-sqlserver数据库备份还原/" title="SqlServer 备份还原"><img class="cover" src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2019-07-23</div><div class="title">SqlServer 备份还原</div></div></a></div><div><a href="/2019/07/23/数据库-Oracle-Oracle使用正则表达式/" title="Oracle中使用正则表达式"><img class="cover" src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" alt="cover"><div class="content is-center"><div class="date"><i class="far fa-calendar-alt fa-fw"></i> 2019-07-23</div><div class="title">Oracle中使用正则表达式</div></div></a></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="card-info-avatar is-center"><img class="avatar-img" src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/3FD9B055-6361-49B7-B8CE-5BA9144BD27F.JPG" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/><div class="author-info__name">方陈勇</div><div class="author-info__description">一路向前</div></div><div class="card-info-data"><div class="card-info-data-item is-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">43</div></a></div><div class="card-info-data-item is-center"><a href="/tags/"><div class="headline">标签</div><div class="length-num">51</div></a></div><div class="card-info-data-item is-center"><a href="/categories/"><div class="headline">分类</div><div class="length-num">53</div></a></div></div><a class="button--animated" id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/fangchenyong"><i class="fab fa-github"></i><span>Follow Me</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://github.com/fangchenyong" target="_blank" title="Github"><i class="fab fa-github"></i></a><a class="social-icon" href="mailto:1013659102@qq.com" target="_blank" title="Email"><i class="fas fa-envelope"></i></a></div></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn card-announcement-animation"></i><span>公告</span></div><div class="announcement_content">个人笔记，如有疑问请联系 QQ:1013659102。</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AF%AF%E5%88%A0%E6%81%A2%E5%A4%8D"><span class="toc-number">1.</span> <span class="toc-text">数据库误删恢复</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%A4%84%E7%90%86%E9%94%81%E5%AE%9A"><span class="toc-number">2.</span> <span class="toc-text">处理锁定</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E5%8E%86%E5%8F%B2%E6%89%A7%E8%A1%8C%E8%AE%B0%E5%BD%95"><span class="toc-number">3.</span> <span class="toc-text">查询历史执行记录</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%A7%A6%E5%8F%91%E5%99%A8%E5%92%8C%E5%BA%8F%E5%88%97"><span class="toc-number">4.</span> <span class="toc-text">触发器和序列</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%85%B3%E8%81%94%E6%9B%B4%E6%96%B0"><span class="toc-number">5.</span> <span class="toc-text">关联更新</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%A1%8C%E8%BD%AC%E5%88%97"><span class="toc-number">6.</span> <span class="toc-text">行转列</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%A1%A8%E7%A9%BA%E9%97%B4%E4%B8%8D%E8%B6%B3"><span class="toc-number">7.</span> <span class="toc-text">表空间不足</span></a></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item"><a class="thumbnail" href="/2021/05/06/%E7%AE%97%E6%B3%95-%E6%8F%92%E5%85%A5%E6%8E%92%E5%BA%8F/" title="算法"><img src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="算法"/></a><div class="content"><a class="title" href="/2021/05/06/%E7%AE%97%E6%B3%95-%E6%8F%92%E5%85%A5%E6%8E%92%E5%BA%8F/" title="算法">算法</a><time datetime="2021-05-05T16:00:00.000Z" title="发表于 2021-05-06 00:00:00">2021-05-06</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2021/05/06/%E7%AE%97%E6%B3%95-%E9%80%89%E6%8B%A9%E6%8E%92%E5%BA%8F/" title="选择排序"><img src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="选择排序"/></a><div class="content"><a class="title" href="/2021/05/06/%E7%AE%97%E6%B3%95-%E9%80%89%E6%8B%A9%E6%8E%92%E5%BA%8F/" title="选择排序">选择排序</a><time datetime="2021-05-05T16:00:00.000Z" title="发表于 2021-05-06 00:00:00">2021-05-06</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2021/04/12/Java-%E6%B7%B1%E5%85%A5%E7%90%86%E8%A7%A3Java%E8%99%9A%E6%8B%9F%E6%9C%BA/" title="深入理解Java虚拟机"><img src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="深入理解Java虚拟机"/></a><div class="content"><a class="title" href="/2021/04/12/Java-%E6%B7%B1%E5%85%A5%E7%90%86%E8%A7%A3Java%E8%99%9A%E6%8B%9F%E6%9C%BA/" title="深入理解Java虚拟机">深入理解Java虚拟机</a><time datetime="2021-04-11T16:00:00.000Z" title="发表于 2021-04-12 00:00:00">2021-04-12</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2021/03/21/%E9%9D%A2%E8%AF%95-%E5%B9%B6%E5%8F%91%E3%80%81%E5%A4%9A%E7%BA%BF%E7%A8%8B/" title="面试题-并发编程"><img src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="面试题-并发编程"/></a><div class="content"><a class="title" href="/2021/03/21/%E9%9D%A2%E8%AF%95-%E5%B9%B6%E5%8F%91%E3%80%81%E5%A4%9A%E7%BA%BF%E7%A8%8B/" title="面试题-并发编程">面试题-并发编程</a><time datetime="2021-03-20T16:00:00.000Z" title="发表于 2021-03-21 00:00:00">2021-03-21</time></div></div><div class="aside-list-item"><a class="thumbnail" href="/2021/03/20/%E9%9D%A2%E8%AF%95-%E9%9B%86%E5%90%88/" title="面试题-集合框架"><img src="https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/BEF238F4E59CF4D91A694FE9C5DBC030.JPG" onerror="this.onerror=null;this.src='/img/404.jpg'" alt="面试题-集合框架"/></a><div class="content"><a class="title" href="/2021/03/20/%E9%9D%A2%E8%AF%95-%E9%9B%86%E5%90%88/" title="面试题-集合框架">面试题-集合框架</a><time datetime="2021-03-19T16:00:00.000Z" title="发表于 2021-03-20 00:00:00">2021-03-20</time></div></div></div></div></div></div></main><footer id="footer" style="background-image: url('https://fangchenyong.oss-cn-hangzhou.aliyuncs.com/3FD9B055-6361-49B7-B8CE-5BA9144BD27F.JPG')"><div id="footer-wrap"><div class="copyright">&copy;2019 - 2021 By 方陈勇</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div><div class="footer_custom_text">人生没有退路！</div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="浅色和深色模式转换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="回到顶部"><i class="fas fa-arrow-up"></i></button></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><div class="js-pjax"></div><script id="canvas_nest" defer="defer" color="0,0,255" opacity="0.7" zIndex="-1" count="99" mobile="false" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc@1/dist/canvas-nest.min.js"></script></div></body></html>